﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
namespace AirlineCommon
{
    public class Ticket
    {
        private string _TicketNo;

        public string TicketNo
        {
            get { return _TicketNo; }
            set { _TicketNo = value; }
        }
        private string _PNRNo;

        public string PNRNo
        {
            get { return _PNRNo; }
            set { _PNRNo = value; }
        }
        private float _Amount;

        public float Amount
        {
            get { return _Amount; }
            set { _Amount = value; }
        }
        private DateTime _TranDate;

        public DateTime TranDate
        {
            get { return _TranDate; }
            set { _TranDate = value; }
        }
        private string _TranType;

        public string TranType
        {
            get { return _TranType; }
            set { _TranType = value; }
        }
        private PassengerCommon _passComm;
        public PassengerCommon PassengerCommon
        {
            get
            {
                return _passComm;
            }
            set
            {
                _passComm = value;
            }
        }
        public string GeneratePNRno()
        {
            string strMaxPnrNo, strNewPnrNo = "";
            int intMaxPnrNo, intNewPnrNo;
            SqlConnection objDBCon = new SqlConnection(DAL.DatabaseProvider.AirlineConnectionString);
            string sQry = "Select Max(PnrNo) from Passengers";
            DataSet dsPnrNo = SqlHelper.ExecuteDataset(objDBCon, CommandType.Text, sQry);
            if (dsPnrNo.Tables[0].Rows[0].IsNull(0))
            {
                strNewPnrNo = "00001";
            }
            else
            {
                strMaxPnrNo = dsPnrNo.Tables[0].Rows[0][0].ToString();
                intMaxPnrNo = int.Parse(strMaxPnrNo);
                intNewPnrNo = intMaxPnrNo + 1;
                for (int i = 0; i < 5 - intNewPnrNo.ToString().Length; i++)
                {
                    strNewPnrNo += "0";
                }
                strNewPnrNo += intNewPnrNo.ToString();
            }
            return strNewPnrNo;
        }
        public void InsertDaillyCollection()
        {
            string InsertSql = "INSERT INTO [DailyCollections] ([PNRNo], [TranDate], [TranType], [Amount]) VALUES (@PNRNo, @TranDate, @TranType, @Amount)";
            SqlConnection conn = new SqlConnection(DAL.DatabaseProvider.AirlineConnectionString);
            SqlParameter pPNRNo = new SqlParameter("@PNRNo", _PNRNo );
            SqlParameter pTranDate = new SqlParameter("@TranDate", _TranDate );
            SqlParameter pTranType = new SqlParameter("@TranType", _TranType);
            SqlParameter pAmount = new SqlParameter("@Amount", _Amount);
            int HowManyRows = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, InsertSql,
               pPNRNo,
               pTranDate,
               pTranType,
               pAmount
               );
            if (HowManyRows != 1)
                throw new Exception("Insert DailyCollections fail.");
            // sqlComm.Dispose();
            conn.Close();
            return;
        }
        public int InsertDaillyCollection(SqlTransaction trans)
        {
            try
            {
                string InsertSql = "INSERT INTO [DailyCollections] ([PNRNo], [TranDate], [TranType], [Amount]) VALUES (@PNRNo, @TranDate, @TranType, @Amount)";
                SqlConnection conn = new SqlConnection(DAL.DatabaseProvider.AirlineConnectionString);
                SqlParameter pPNRNo = new SqlParameter("@PNRNo", _PNRNo);
                SqlParameter pTranDate = new SqlParameter("@TranDate", _TranDate);
                SqlParameter pTranType = new SqlParameter("@TranType", _TranType);
                SqlParameter pAmount = new SqlParameter("@Amount", _Amount);
                int HowManyRows = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, InsertSql,
                   pPNRNo,
                   pTranDate,
                   pTranType,
                   pAmount
                   );
                return HowManyRows;
            }
            catch
            {
                return -1;
            }
            
        }
        public string GetPassengerClassByPNRNo(string PNRNo)
        {
            SqlConnection conn = new SqlConnection(DAL.DatabaseProvider.AirlineConnectionString);
            string sql = "Select [Class] from Passengers Where PNRNo='" + PNRNo + "'";
            DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            if (ds.Tables[0].Rows.Count == 0) return string.Empty;
            return ds.Tables[0].Rows[0][0].ToString();
        }
        public float GetFareAmount(string PNRNo)
        {
            string strCONN = DAL.DatabaseProvider.AirlineConnectionString;
            string sql = "Select Amount from  DailyCollections where PNRNo=@PNRNo And TranType='Collection'";
            float _Fare = 0;
            try
            {
                SqlParameter pSecID = new SqlParameter("@PNRNo", PNRNo );
                DataSet ds = SqlHelper.ExecuteDataset(strCONN, CommandType.Text, sql, pSecID);
                if (ds.Tables[0].Rows.Count > 0)
                    _Fare = float.Parse(ds.Tables[0].Rows[0][0].ToString());
                else
                    _Fare = 0;
                return _Fare;
            }
            catch (Exception ex)
            {
                //Log Exception (will be provided in next release)
                AppLogger.Error("Error Executing in Get Fare Amount", ex, "Ticket", "GetFareAmount");
                throw;
            }
        }
        public float GetRefundAmount(string PNRNo)
        {
            string strCONN = DAL.DatabaseProvider.AirlineConnectionString;
            string sql = "Select Amount from  DailyCollections where PNRNo=@PNRNo And TranType='Refund'";
            float _Fare = 0;
            try
            {
                SqlParameter pSecID = new SqlParameter("@PNRNo", PNRNo);
                DataSet ds = SqlHelper.ExecuteDataset(strCONN, CommandType.Text, sql, pSecID);
                if (ds.Tables[0].Rows.Count > 0)
                    _Fare = float.Parse(ds.Tables[0].Rows[0][0].ToString());
                else
                    _Fare = 0;
                return _Fare;
            }
            catch (Exception ex)
            {
                //Log Exception (will be provided in next release)
                AppLogger.Error("Error Executing in Get Refund Amount", ex, "Ticket", "GetRefundAmount");
                throw;
            }
        }
    }
}
